package com.seabook.store.purchase.dao;

import java.sql.PreparedStatement;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Calendar;
import java.util.List;

import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.seabook.store.goodmgr.domain.Product;
import com.seabook.store.purchase.domain.Item;
import com.seabook.store.purchase.domain.Order;
import com.seabook.store.purchase.domain.LineItem;
import com.seabook.store.purchase.domain.Order.Status;

public class OrderDaoImpl extends JdbcDaoSupport implements OrderDao{
    
    private final String SQL_INSERT_ORDER = " call saveOrder (?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?)";
    
    private final String SQL_UPDATE_ORDERSTATUS = " update orders set status = ? where orderId = ?";
      
    private final String SQL_INSERT_LINEITEMS = "insert into lineitem values (?,?,?,?,?)";
    
    private final String SQL_QUERY_ORDER = " select orders.* from orders where orders.orderId = ? ";
    
    private final String SQL_QUERY_LINEITEM = "select lineitem.*, product.productNo, product.name, product.imgUrl from lineitem, product where lineitem.productId = product.productId and lineitem.orderId = ? ";
    
    private final String SQL_QUERY_ORDERS = " select orders.* from orders where orders.userId = ? ";

    private OrderRowMapper orderMapper = new OrderRowMapper();
    
    private LineItemRowMapper itemMapper = new LineItemRowMapper();
    
    public Order insertOrder(Order order) {
        Calendar cal = Calendar.getInstance();
        order.setOrderDate(cal.getTime());
        cal.add(Calendar.DAY_OF_MONTH, +7);
        order.setExpireDate(cal.getTime());
        int orderId =  insertNewOrder(order);
        order.setOrderId(orderId);
        order.setStatus(Order.Status.UNPAID);
        this.saveOrderItems(order.getLineItems(), orderId);
        return order;
    }
    
    public Order getOrder(int orderId){
        Order order = this.getJdbcTemplate().queryForObject(SQL_QUERY_ORDER, new Object[]{orderId}, orderMapper);
        order.setLineItems(this.getOrderItems(orderId));
        return order;
    }
    
    private int insertNewOrder(Order order){
        return this.getJdbcTemplate().queryForInt(SQL_INSERT_ORDER, new Object[]{
                order.getUserId(),
                order.getOrderDate(),
                order.getShipAddress(),
                order.getShipAddress2(),
                order.getShipCity(),
                order.getShipState(),
                order.getShipSurburb(),
                order.getShipZip(),
                order.getShipCountry(),
                order.getBillAddress(),
                order.getBillAddress2(),
                order.getBillCity(),
                order.getBillState(),
                order.getBillSurburb(),
                order.getBillZip(),
                order.getBillCountry(),
                order.getSubTotal(),
                order.getShippingMethod(),
                order.getShippingFee(),
                order.getTotalPrice(),
                order.getBillToFirstName(),
                order.getBillToLastName(),
                order.getShipToFirstName(),
                order.getShipToLastName(),
                order.getPayMethod(),
                order.getExpireDate(),
                "AU",
                Order.Status.UNPAID.toString()         
        });
    }
    
    private void saveOrderItems(final List<LineItem> lineItems, final int orderId){
        this.getJdbcTemplate().batchUpdate(SQL_INSERT_LINEITEMS, new BatchPreparedStatementSetter(){
            public int getBatchSize() {
                // TODO Auto-generated method stub
                return lineItems.size();
            }
            public void setValues(PreparedStatement ps, int row)
                    throws SQLException {
                LineItem litem = lineItems.get(row);
                ps.setInt(1, orderId);
                ps.setInt(2, litem.getLineNumber());
                ps.setInt(3, litem.getItem().getProduct().getProductId());
                ps.setInt(4, litem.getQuantity());
                ps.setDouble(5, litem.getUnitPrice());             
            }});
    }
    
    private List<LineItem> getOrderItems (int orderId){
       return this.getJdbcTemplate().query(SQL_QUERY_LINEITEM, new Object[]{orderId}, itemMapper);
    }

    public List<Order> getOrderByUserId(int userId) {
        return this.getJdbcTemplate().query(SQL_QUERY_ORDERS, new Object[]{userId}, orderMapper);
    }
    
    class OrderRowMapper implements RowMapper<Order> {
        public Order mapRow(ResultSet rs, int rowNumber) throws SQLException {
            Order order = new Order();
            order.setOrderId(rs.getInt("orderid"));
            order.setUserId(rs.getInt("userId"));
            order.setOrderDate(rs.getDate("orderdate"));
            order.setBillAddress(rs.getString("billaddr1"));
            order.setBillAddress2(rs.getString("billaddr2"));
            order.setBillCity(rs.getString("billcity"));
            order.setBillCountry(rs.getString("billcountry"));
            order.setBillState(rs.getString("billstate"));
            order.setBillSurburb(rs.getString("billsurburb"));
            order.setBillToFirstName(rs.getString("billtofirstname"));
            order.setBillToLastName(rs.getString("billtolastname"));
            order.setBillZip(rs.getString("billzip"));
            //order.setComment(rs.getString("comment"));
            order.setExpireDate(rs.getDate("exprdate"));
            order.setPayMethod(rs.getString("paymenttype"));
            order.setShipAddress(rs.getString("shipaddr1"));
            order.setShipAddress2(rs.getString("shipaddr2"));
            order.setShipCity(rs.getString("shipcity"));
            order.setShipCountry(rs.getString("shipcountry"));
            order.setShipState(rs.getString("shipstate"));
            order.setShipSurburb(rs.getString("shipsurburb"));
            order.setShipToFirstName(rs.getString("shiptofirstname"));
            order.setShipToLastName(rs.getString("shiptolastname"));
            order.setShipZip(rs.getString("shipzip"));
            order.setShippingFee(rs.getDouble("postage"));
            order.setShippingMethod(rs.getString("shippingtype"));
            order.setStatus(rs.getString("status"));
            order.setTotalPrice(rs.getDouble("totalprice"));
            order.setLineItems(getOrderItems(order.getOrderId()));
            return order;
        }
    }
    
    class LineItemRowMapper implements RowMapper<LineItem>{

        public LineItem mapRow(ResultSet rs, int rowNumber) throws SQLException {
            LineItem litem = new LineItem();
            litem.setItemId(rs.getString("productId"));
            litem.setLineNumber(rs.getInt("linenum"));
            litem.setQuantity(rs.getInt("quantity"));
            litem.setUnitPrice(rs.getDouble("unitprice"));
            litem.setOrderId(rs.getInt("orderId"));
            Item item = new Item();
            Product product = new Product();
            product.setName(rs.getString("name"));
            product.setImgUrls(rs.getString("imgUrl"));
            product.setProductNo(rs.getString("productNo"));
            item.setListPrice(litem.getUnitPrice());
            item.setProduct(product);
            litem.setItem(item);
            return litem;
        }
        
    }

    public void updateOrderStatus(int orderId, Status paid) {
        this.getJdbcTemplate().update(SQL_UPDATE_ORDERSTATUS, new Object[]{paid.toString(), orderId});
    }

}
